Skip Headers

Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 6 How Rules Are Used in Streams

This chapter explains how rules are used in Streams.

This chapter contains these topics:

- Overview of How Rules Are Used in Streams

- Rule Sets and Rule Evaluation of Messages

- System-Created Rules

- Evaluation Contexts Used in Streams

- Streams and Event Contexts

- Streams and Action Contexts

- User-Created Rules, Rule Sets, and Evaluation Contexts

See Also: Chapter 5, "Rules" for more information about rules Chapter 14, "Managing Rules"

## Overview of How Rules Are Used in Streams

In Streams, each of the following mechanisms is called a Streams client because each one is a client of a rules engine, when the mechanism is associated with one or more rule sets:

- Capture process

- Propagation

- Apply process

- Messaging client

Each of these clients can be associated with at most two rule sets: a positive rule set and a negative rule set. A single rule set can be used by multiple capture processes, propagations, apply processes, and messaging clients within the same database. Also, a single rule set can be a positive rule set for one Streams client and a negative rule set for another Streams client.

Figure 6-1 illustrates how multiple clients of a rules engine can use one rule set.

Figure 6-1 One Rule Set Can Be Used by Multiple Clients of a Rules Engine

Description of "Figure 6-1 One Rule Set Can Be Used by Multiple Clients of a Rules Engine"

A Streams client performs a task if a message satisfies its rule sets. In general, a message satisfies the rule sets for a Streams client if no rules in the negative rule set evaluate to `TRUE` for the message, and at least one rule in the positive rule set evaluates to `TRUE` for the message.

"Rule Sets and Rule Evaluation of Messages" contains more detailed information about how a message satisfies the rule sets for a Streams client, including information about Streams client behavior when one or more rule sets are not specified.

Specifically, you use rule sets in Streams to do the following:

- Specify the changes that a capture process captures from the redo log or discards. That is, if a change found in the redo log satisfies the rule sets for a capture process, then the capture process captures the change. If a change found in the redo log causes does not satisfy the rule sets for a capture process, then the capture process discards the change.

- Specify the messages that a propagation propagates from one queue to another or discards. That is, if a message in a queue satisfies the rule sets for a propagation, then the propagation propagates the message. If a message in a queue does not satisfy the rule sets for a propagation, then the propagation discards the message.

- Specify the messages that an apply process retrieves from a queue or discards. That is, if a message in a queue satisfies the rule sets for an apply process, then the message is dequeued and processed by the apply process. If a message in a queue does not satisfy the rule sets for an apply process, then the apply process discards the message.

- Specify the user-enqueued messages that a messaging client dequeues from a queue or discards. That is, if a user-enqueued message in a queue satisfies the rule sets for a messaging client, then the user or application that is using the messaging client dequeues the message. If a user-enqueued message in a queue does not satisfy the rule sets for a messaging client, then the user or application that is using the messaging client discards the message.

In the case of a propagation or an apply process, the messages evaluated against the rule sets can be captured messages or user-enqueued messages.

If there are conflicting rules in the positive rule set associated with a client, then the client performs the task if either rule evaluates to `TRUE`. For example, if a rule in the positive rule set for a capture process contains one rule that instructs the capture process to capture the results of data manipulation language (DML) changes to the `hr.employees` table, but another rule in the rule set instructs the capture process not to capture the results of DML changes to the `hr.employees` table, then the capture process captures these changes.

Similarly, if there are conflicting rules in the negative rule set associated with a client, then the client discards a message if either rule evaluates to `TRUE` for the message. For example, if a rule in the negative rule set for a capture process contains one rule that instructs the capture process to discard the results of DML changes to the `hr.departments` table, but another rule in the rule set instructs the capture process not to discard the results of DML changes to the `hr.departments` table, then the capture process discards these changes.

See Also: For more information about Streams clients: Chapter 2, "Streams Capture Process" "Message Propagation Between Queues" Chapter 4, "Streams Apply Process" "Messaging Clients"

## Rule Sets and Rule Evaluation of Messages

Streams clients perform the following tasks based on rules:

- A capture process captures changes in the redo log, converts the changes into logical change records (LCRs), and enqueues messages containing these LCRs into the capture process queue.

- A propagation propagates either captured messages or user-enqueued messages, or both, from a source queue to a destination queue.

- An apply process dequeues either captured or user-enqueued messages from its queue and applies these messages directly or sends the messages to an apply handler.

- A messaging client dequeues user-enqueued messages from its queue.

These Streams clients are all clients of the rules engine. A Streams client performs its task for a message when the message satisfies the rule sets used by the Streams client. A Streams client can have no rule set, only a positive rule set, only a negative rule set, or both a positive and a negative rule set. The following sections explain how rule evaluation works in each of these cases:

- Streams Client with No Rule Set

- Streams Client with a Positive Rule Set Only

- Streams Client with a Negative Rule Set Only

- Streams Client with Both a Positive and a Negative Rule Set

- Streams Client with One or More Empty Rule Sets

- Summary of Rule Sets and Streams Client Behavior

### Streams Client with No Rule Set

A Streams client with no rule set performs its task for all of the messages it encounters. An empty rule set is not the same as no rule set at all.

A capture process should always have at least one rule set because it must not try to capture changes to unsupported database objects. If a propagation should always propagate all messages in its source queue, or if an apply process should always dequeue all messages in its queue, then removing all rule sets from the propagation or apply process might improve performance.

See Also: "Streams Client with One or More Empty Rule Sets"

### Streams Client with a Positive Rule Set Only

A Streams client with a positive rule set, but no negative rule set, performs its task for a message if any rule in the positive rule set evaluates to `TRUE` for the message. However, if all of the rules in a positive rule set evaluate to `FALSE` for the message, then the Streams client discards the message.

### Streams Client with a Negative Rule Set Only

A Streams client with a negative rule set, but no positive rule set, discards a message if any rule in the negative rule set evaluates to `TRUE` for the message. However, if all of the rules in a negative rule set evaluate to `FALSE` for the message, then the Streams client performs its task for the message.

### Streams Client with Both a Positive and a Negative Rule Set

If Streams client has both a positive and a negative rule set, then the negative rule set is evaluated first for a message. If any rule in the negative rule set evaluates to `TRUE` for the message, then the message is discarded, and the message is never evaluated against the positive rule set.

However, if all of the rules in the negative rule set evaluate to `FALSE` for the message, then the message is evaluated against the positive rule set. At this point, the behavior is the same as when the Streams client only has a positive rule set. That is, the Streams client performs its task for a message if any rule in the positive rule set evaluates to `TRUE` for the message. If all of the rules in a positive rule set evaluate to `FALSE` for the message, then the Streams client discards the message.

### Streams Client with One or More Empty Rule Sets

A Streams client can have one or more empty rule sets. A Streams client behaves in the following ways if it has one or more empty rule sets:

- If a Streams client has no positive rule set, and its negative rule set is empty, then the Streams client performs its task for all messages.

- If a Streams client has both a positive and a negative rule set, and the negative rule set is empty but its positive rule set contains rules, then the Streams client performs its task based on the rules in the positive rule set.

- If a Streams client has a positive rule set that is empty, then the Streams client discards all messages, regardless of the state of its negative rule set.

### Summary of Rule Sets and Streams Client Behavior

Table 6-1 summarizes the Streams client behavior described in the previous sections.

Table 6-1 Rule Sets and Streams Client Behavior

Negative Rule Set | Positive Rule Set | Streams Client Behavior
--- | --- | ---
None | None | Performs its task for all messages
None | Exists with rules | Performs its task for messages that evaluate to `TRUE` against the positive rule set
Exists with rules | None | Discards messages that evaluate to `TRUE` against the negative rule set, and performs its task for all other messages
Exists with rules | Exists with rules | Discards messages that evaluate to `TRUE` against the negative rule set, and performs its task for remaining messages that evaluate to `TRUE` against the positive rule set. The negative rule set is evaluated first.
Exists but is empty | None | Performs its task for all messages
Exists but is empty | Exists with rules | Performs its task for messages that evaluate to `TRUE` against the positive rule set
None | Exists but is empty | Discards all messages
Exists but is empty | Exists but is empty | Discards all messages
Exists with rules | Exists but is empty | Discards all messages

## System-Created Rules

A Streams client performs its task for a message if the message satisfies its rule sets. A system-created rule is created by the `DBMS_STREAMS_ADM` package and can specify one of the following levels of granularity: table, schema, or global. This section describes each of these levels. You can specify more than one level for a particular task. For example, you can instruct a single apply process to perform table-level apply for specific tables in the `oe` schema and schema-level apply for the entire `hr` schema. In addition, a single rule pertains to either the results of data manipulation language (DML) changes or data definition language (DDL) changes. So, for example, you must use at least two system-created rules to include all of the changes to a particular table: one rule for the results of DML changes and another rule for DDL changes. The results of a DML change are the row changes recorded in the redo log because of the DML change, or the row LCRs in a queue that encapsulate each row change.

Table 6-2 shows what each level of rule means for each Streams task. Remember that a negative rule set is evaluated before a positive rule set.

Table 6-2 Types of Tasks and Rule Levels

Task | Table Rule | Schema Rule | Global Rule
--- | --- | --- | ---
Capture with a capture process | If the table rule is in a negative rule set, then discard the changes in the redo log for the specified table. If the table rule is in a positive rule set, then capture all or a subset of the changes in the redo log for the specified table, convert them into logical change records (LCRs), and enqueue them. | If the schema rule is in a negative rule set, then discard the changes in the redo log for the schema itself and for the database objects in the specified schema. If the schema rule is in a positive rule set, then capture the changes in the redo log for the schema itself and for the database objects in the specified schema, convert them into LCRs, and enqueue them. | If the global rule is in a negative rule set, then discard the changes to all of the database objects in the database. If the global rule is in a positive rule set, then capture the changes to all of the database objects in the database, convert them into LCRs, and enqueue them.
Propagate with a propagation | If the table rule is in a negative rule set, then discard the LCRs relating to the specified table in the source queue. If the table rule is in a positive rule set, then propagate all or a subset of the LCRs relating to the specified table in the source queue to the destination queue. | If the schema rule is in a negative rule set, then discard the LCRs related to the specified schema itself and the LCRs related to database objects in the schema in the source queue. If the schema rule is in a positive rule set, then propagate the LCRs related to the specified schema itself and the LCRs related to database objects in the schema in the source queue to the destination queue. | If the global rule is in a negative rule set, then discard all of the LCRs in the source queue. If the global rule is in a positive rule set, then propagate all of the LCRs in the source queue to the destination queue.
Apply with an apply process | If the table rule is in a negative rule set, then discard the LCRs in the queue relating to the specified table. If the table rule is in a positive rule set, then apply all or a subset of the LCRs in the queue relating to the specified table. | If the schema rule is in a negative rule set, then discard the LCRs in the queue relating to the specified schema itself and the database objects in the schema. If the schema rule is in a positive rule set, then apply the LCRs in the queue relating to the specified schema itself and the database objects in the schema. | If the global rule is in a negative rule set, then discard all of the LCRs in the queue. If the global rule is in a positive rule set, then apply all of the LCRs in the queue.
Dequeue with a messaging client | If the table rule is in a negative rule set, then, when the messaging client is invoked, discard the user-enqueued LCRs relating to the specified table in the queue. If the table rule is in a positive rule set, then, when the messaging client is invoked, dequeue all or a subset of the user-enqueued LCRs relating to the specified table in the queue. | If the schema rule is in a negative rule set, then, when the messaging client is invoked, discard the user-enqueued LCRs relating to the specified schema itself and the database objects in the schema in the queue. If the schema rule is in a positive rule set, then, when the messaging client is invoked, dequeue the user-enqueued LCRs relating to the specified schema itself and the database objects in the schema in the queue. | If the global rule is in a negative rule set, then, when the messaging client is invoked, discard all of the user-enqueued LCRs in the queue. If the global rule is in a positive rule set, then, when the messaging client is invoked, dequeue all of the user-enqueued LCRs in the queue.

You can use procedures in the `DBMS_STREAMS_ADM` package to create rules at each of these levels. A system-created rule can include conditions that modify the Streams client behavior beyond the descriptions in Table 6-2. For example, some rules can specify a particular source database for LCRs, and, in this case, the rule evaluates to `TRUE` only if an LCR originated at the specified source database. Table 6-3 lists the types of system-created rule conditions that can be specified in the rules created by the `DBMS_STREAMS_ADM` package.

Table 6-3 System-Created Rule Conditions Created by DBMS_STREAMS_ADM Package

Rule Condition Evaluates to TRUE for | Streams Client | Create Using Procedure
--- | --- | ---
All row changes recorded in the redo log because of DML changes to any of the tables in a particular database | Capture Process | `ADD_GLOBAL_RULES`
All DDL changes recorded in the redo log to any of the database objects in a particular database | Capture Process | `ADD_GLOBAL_RULES`
All row changes recorded in the redo log because of DML changes to any of the tables in a particular schema | Capture Process | `ADD_SCHEMA_RULES`
All DDL changes recorded in the redo log to a particular schema and any of the database objects in the schema | Capture Process | `ADD_SCHEMA_RULES`
All row changes recorded in the redo log because of DML changes to a particular table | Capture Process | `ADD_TABLE_RULES`
All DDL changes recorded in the redo log to a particular table | Capture Process | `ADD_TABLE_RULES`
All row changes recorded in the redo log because of DML changes to a subset of rows in a particular table | Capture Process | `ADD_SUBSET_RULES`
All row LCRs in the source queue | Propagation | `ADD_GLOBAL_PROPAGATION_RULES`
All DDL LCRs in the source queue | Propagation | `ADD_GLOBAL_PROPAGATION_RULES`
All row LCRs in the source queue relating to the tables in a particular schema | Propagation | `ADD_SCHEMA_PROPAGATION_RULES`
All DDL LCRs in the source queue relating to a particular schema and any of the database objects in the schema | Propagation | `ADD_SCHEMA_PROPAGATION_RULES`
All row LCRs in the source queue relating to a particular table | Propagation | `ADD_TABLE_PROPAGATION_RULES`
All DDL LCRs in the source queue relating to a particular table | Propagation | `ADD_TABLE_PROPAGATION_RULES`
All row LCRs in the source queue relating to a subset of rows in a particular table | Propagation | `ADD_SUBSET_PROPAGATION_RULES`
All user-enqueued messages in the source queue of the specified type that satisfy the user-specified rule condition | Propagation | `ADD_MESSAGE_PROPAGATION_RULE`
All row LCRs in the queue used by the apply process | Apply Process | `ADD_GLOBAL_RULES`
All DDL LCRs in the queue used by the apply process | Apply Process | `ADD_GLOBAL_RULES`
All row LCRs in the queue used by the apply process relating to the tables in a particular schema | Apply Process | `ADD_SCHEMA_RULES`
All DDL LCRs in the queue used by the apply process relating to a particular schema and any of the database objects in the schema | Apply Process | `ADD_SCHEMA_RULES`
All row LCRs in the queue used by the apply process relating to a particular table | Apply Process | `ADD_TABLE_RULES`
All DDL LCRs in the queue used by the apply process relating to a particular table | Apply Process | `ADD_TABLE_RULES`
All row LCRs in the queue used by the apply process relating to a subset of rows in a particular table | Apply Process | `ADD_SUBSET_RULES`
All user-enqueued messages in the queue used by the apply process of the specified type that satisfy the user-specified rule condition | Apply Process | `ADD_MESSAGE_RULE`
All user-enqueued row LCRs in the queue used by the messaging client | Messaging Client | `ADD_GLOBAL_RULES`
All user-enqueued DDL LCRs in the queue used by the messaging client | Messaging Client | `ADD_GLOBAL_RULES`
All user-enqueued row LCRs in the queue used by the messaging client relating to the tables in a particular schema | Messaging Client | `ADD_SCHEMA_RULES`
All user-enqueued DDL LCRs in the queue used by the messaging client relating to a particular schema and any of the database objects in the schema | Messaging Client | `ADD_SCHEMA_RULES`
All user-enqueued row LCRs in the messaging client's queue relating to a particular table | Messaging Client | `ADD_TABLE_RULES`
All user-enqueued DDL LCRs in the queue used by the messaging client relating to a particular table | Messaging Client | `ADD_TABLE_RULES`
All user-enqueued row LCRs in the queue used by the messaging client relating to a subset of rows in a particular table | Messaging Client | `ADD_SUBSET_RULES`
All user-enqueued messages in the queue used by the messaging client of the specified type that satisfy the user-specified rule condition | Messaging Client | `ADD_MESSAGE_RULE`

Each procedure listed in Table 6-3 does the following:

- Creates a capture process, propagation, apply process, or messaging client if it does not already exist.

- Creates a rule set for the specified capture process, propagation, apply process, or messaging client if a rule set does not already exist for it. The rule set can be a positive rule set or a negative rule set. You can create each type of rule set by running the procedure at least twice.

- Creates zero or more rules and adds the rules to the rule set for the specified capture process, propagation, apply process, or messaging client. Based on your specifications when you run one of these procedures, the procedure adds the rules either to the positive rule set or to the negative rule set.

Except for the `ADD_MESSAGE_RULE` and `ADD_MESSAGE_PROPAGATION_RULE` procedures, these procedures create rule sets that use the `SYS.STREAMS$_EVALUATION_CONTEXT` evaluation context, which is an Oracle-supplied evaluation context for Streams environments. Global, schema, table, and subset rules use the `SYS.STREAMS$_EVALUATION_CONTEXT` evaluation context.

However, when you create a rule using either the `ADD_MESSAGE_RULE` or the `ADD_MESSAGE_PROPAGATION_RULE` procedure, the rule uses a system-generated evaluation context that is customized specifically for each message type. Rule sets created by the `ADD_MESSAGE_RULE` or the `ADD_MESSAGE_PROPAGATION_RULE` procedure do not have an evaluation context.

Except for `ADD_SUBSET_RULES`, `ADD_SUBSET_PROPAGATION_RULES`, `ADD_MESSAGE_RULE`, and `ADD_MESSAGE_PROPAGATION_RULE`, these procedures create either zero, one, or two rules. If you want to perform the Streams task for only the row changes resulting from DML changes or only for only DDL changes, then only one rule is created. If, however, you want to perform the Streams task for both the results of DML changes and DDL changes, then a rule is created for each. If you create a DML rule for a table now, then you can create a DDL rule for the same table in the future without modifying the DML rule created earlier. The same applies if you create a DDL rule for a table first and a DML rule for the same table in the future.

The `ADD_SUBSET_RULES` and `ADD_SUBSET_PROPAGATION_RULES` procedures always create three rules for three different types of DML operations on a table: `INSERT`, `UPDATE`, and `DELETE`. These procedures do not create rules for DDL changes to a table. You can use the `ADD_TABLE_RULES` or `ADD_TABLE_PROPAGATION_RULES` procedure to create a DDL rule for a table. In addition, you can add subset rules to positive rule sets only, not to negative rule sets.

The `ADD_MESSAGE_RULE` and `ADD_MESSAGE_PROPAGATION_RULE` procedures always create one rule with a user-specified rule condition. These procedures create rules for user-enqueued messages. They do not create rules for the results of DML changes or DDL changes to a table.

When you create propagation rules for captured messages, Oracle recommends that you specify a source database for the changes. An apply process uses transaction control messages to assemble captured messages into committed transactions. These transaction control messages, such as `COMMIT` and `ROLLBACK`, contain the name of the source database where the message occurred. To avoid unintended cycling of these messages, propagation rules should contain a condition specifying the source database, and you accomplish this by specifying the source database when you create the propagation rules.

The following sections describe system-created rules in more detail:

- Global Rules

- Schema Rules

- Table Rules

- Subset Rules

- Message Rules

- System-Created Rules and Negative Rule Sets

- System-Created Rules with Added User-Defined Conditions

Note: To create rules with more complex rule conditions, such as rules that use the `NOT` or `OR` logical conditions, either use the `and_condition` parameter, which is available with some of the procedures in the `DBMS_STREAMS_ADM` package, or use the `DBMS_RULE_ADM` package. Each example in the sections that follow should be completed by a Streams administrator that has been granted the appropriate privileges, unless specified otherwise. Some of the examples in this section have additional prerequisites. For example, a queue specified by a procedure parameter must exist.

See Also: "Rule Sets and Rule Evaluation of Messages" for information about how messages satisfy the rule sets for a Streams client Oracle Database PL/SQL Packages and Types Reference for more information about the `DBMS_STREAMS_ADM` package and the `DBMS_RULE_ADM` package "Evaluation Contexts Used in Streams" "Logical Change Records (LCRs)" "Complex Rule Conditions"

### Global Rules

When you use a rule to specify a Streams task that is relevant either to an entire database or to an entire queue, you are specifying a global rule. You can specify a global rule for DML changes, a global rule for DDL changes, or a global rule for each type of change (two rules total).

A single global rule in the positive rule set for a capture process means that the capture process captures the results of either all DML changes or all DDL changes to the source database. A single global rule in the negative rule set for a capture process means that the capture process discards the results of either all DML changes or all DDL changes to the source database.

A single global rule in the positive rule set for a propagation means that the propagation propagates either all row LCRs or all DDL LCRs in the source queue to the destination queue. A single global rule in the negative rule set for a propagation means that the propagation discards either all row LCRs or all DDL LCRs in the source queue.

A single global rule in the positive rule set for an apply process means that the apply process applies either all row LCRs or all DDL LCRs in its queue for a specified source database. A single global rule in the negative rule set for an apply process means that the apply process discards either all row LCRs or all DDL LCRs in its queue for a specified source database.

If you want to use global rules, but you are concerned about changes to database objects that are not supported by Streams, then you can create rules using the `DBMS_RULE_ADM` package to discard unsupported changes.

See Also: "Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs"

#### Global Rules Example

Suppose you use the `ADD_GLOBAL_RULES` procedure in the `DBMS_STREAMS_ADM` package to instruct a Streams capture process to capture all DML changes and DDL changes in a database.

Run the `ADD_GLOBAL_RULES` procedure to create the rules:

    BEGIN
      DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        streams_type        =>  'capture',
        streams_name        =>  'capture',
        queue_name          =>  'streams_queue',
        include_dml         =>  true,
        include_ddl         =>  true,
        include_tagged_lcr  =>  false,
        source_database     =>  NULL,
        inclusion_rule      =>  true);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rules are added to the positive rule set for the capture process.

`NULL` can be specified for the `source_database` parameter because rules are being created for a local capture process. You can also specify the global name of the local database. When creating rules for a downstream capture process or apply process using `ADD_GLOBAL_RULES`, specify a source database name.

The `ADD_GLOBAL_RULES` procedure creates two rules: one for row LCRs (which contain the results of DML changes) and one for DDL LCRs.

Here is the rule condition used by the row LCR rule:

    (:dml.is_null_tag() = 'Y' )

Notice that the condition in the DML rule begins with the variable `:dml`. The value is determined by a call to the specified member function for the row LCR being evaluated. So, `:dml.is_null_tag()` is a call to the `IS_NULL_TAG` member function for the row LCR being evaluated.

Here is the rule condition used by the DDL LCR rule:

    (:ddl.is_null_tag() = 'Y' )

Notice that the condition in the DDL rule begins with the variable `:ddl`. The value is determined by a call to the specified member function for the DDL LCR being evaluated. So, `:ddl.is_null_tag()` is a call to the `IS_NULL_TAG` member function for the DDL LCR being evaluated.

For a capture process, these conditions indicate that the tag must be `NULL` in a redo record for the capture process to capture a change. For a propagation, these conditions indicate that the tag must be `NULL` in an LCR for the propagation to propagate the LCR. For an apply process, these conditions indicate that the tag must be `NULL` in an LCR for the apply process to apply the LCR.

Given the rules created by this example in the positive rule set for the capture process, the capture process captures all supported DML and DDL changes made to the database.

Caution: If you add global rules to the positive rule set for a capture process, then make sure you add rules to the negative capture process rule set to exclude database objects that are not support by Streams. Query the `DBA_STREAMS_UNSUPPORTED` data dictionary view to determine which database objects are not supported by Streams. If unsupported database objects are not excluded, then capture errors will result.

See Also: "Listing the Database Objects that Are Not Compatible with Streams"

#### System-Created Global Rules Avoid Empty Rule Conditions Automatically

You can omit the `is_null_tag` condition in system-created rules by specifying `true` for the `include_tagged_lcr` parameter when you run a procedure in the `DBMS_STREAMS_ADM` package. For example, the following `ADD_GLOBAL_RULES` procedure creates rules without the `is_null_tag` condition:

    BEGIN DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
       streams_type        =>  'capture',
       streams_name        =>  'capture_002',
       queue_name          =>  'streams_queue',
       include_dml         =>  true,
       include_ddl         =>  true,
       include_tagged_lcr  =>  true,
       source_database     =>  NULL,
       inclusion_rule      =>  true);
    END;
    /

When you set the `include_tagged_lcr` parameter to `true` for a global rule, and the `source_database_name` parameter is set to `NULL`, the rule condition used by the row LCR rule is the following:

    (( :dml.get_source_database_name()>=' ' OR
    :dml.get_source_database_name()<=' ') )

Here is the rule condition used by the DDL LCR rule:

    (( :ddl.get_source_database_name()>=' ' OR
    :ddl.get_source_database_name()<=' ') )

The system-created global rules contain these conditions to enable all row and DDL LCRs to evaluate to `TRUE`.

These rule conditions are specified to avoid `NULL` rule conditions for these rules. `NULL` rule conditions are not supported. In this case, if you want to capture all DML and DDL changes to a database, and you do not want to use any rule-based transformations for these changes upon capture, then you can choose to run the capture process without a positive rule set instead of specifying global rules.

Note: When you create a capture process using a procedure in the `DBMS_STREAMS_ADM` package and generate one or more rules for the capture process, the objects for which changes are captured are prepared for instantiation automatically, unless it is a downstream capture process and there is no database link from the downstream database to the source database. The capture process does not capture some types of DML and DDL changes, and it does not capture changes made in the `SYS`, `SYSTEM`, or `CTXSYS` schemas.

See Also: Oracle Streams Replication Administrator's Guide for more information about capture process rules and preparation for instantiation Chapter 2, "Streams Capture Process" for more information about the capture process and for detailed information about which DML and DDL statements are captured by a capture process Chapter 5, "Rules" for more information about variables in conditions Oracle Streams Replication Administrator's Guide for more information about Streams tags "Rule Sets and Rule Evaluation of Messages" for more information about running a capture process with no positive rule set

### Schema Rules

When you use a rule to specify a Streams task that is relevant to a schema, you are specifying a schema rule. You can specify a schema rule for DML changes, a schema rule for DDL changes, or a schema rule for each type of change to the schema (two rules total).

A single schema rule in the positive rule set for a capture process means that the capture process captures either the DML changes or the DDL changes to the schema. A single schema rule in the negative rule set for a capture process means that the capture process discards either the DML changes or the DDL changes to the schema.

A single schema rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema. A single schema rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the schema.

A single schema rule in the positive rule set for an apply process means that the apply process applies either the row LCRs or the DDL LCRs in its queue that contain changes to the schema. A single schema rule in the negative rule set for an apply process means that the apply process discards either the row LCRs or the DDL LCRs in its queue that contain changes to the schema.

If you want to use schema rules, but you are concerned about changes to database objects in a schema that are not supported by Streams, then you can create rules using the `DBMS_RULE_ADM` package to discard unsupported changes.

See Also: "Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs"

#### Schema Rule Example

Suppose you use the `ADD_SCHEMA_PROPAGATION_RULES` procedure in the `DBMS_STREAMS_ADM` package to instruct a Streams propagation to propagate row LCRs and DDL LCRs relating to the `hr` schema from a queue at the `dbs1.net` database to a queue at the `dbs2.net` database.

Run the `ADD_SCHEMA_PROPAGATION_RULES` procedure at `dbs1.net` to create the rules:

    BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
        schema_name              =>  'hr',
        streams_name             =>  'dbs1_to_dbs2',
        source_queue_name        =>  'streams_queue',
        destination_queue_name   =>  'streams_queue@dbs2.net',
        include_dml              =>  true,
        include_ddl              =>  true,
        include_tagged_lcr       =>  false,
        source_database          =>  'dbs1.net',
        inclusion_rule           =>  true);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rules are added to the positive rule set for the propagation.

The `ADD_SCHEMA_PROPAGATION_RULES` procedure creates two rules: one for row LCRs (which contain the results of DML changes) and one for DDL LCRs.

Here is the rule condition used by the row LCR rule:

    ((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y'
    and :dml.get_source_database_name() = 'DBS1.NET' )

Here is the rule condition used by the DDL LCR rule:

    ((:ddl.get_object_owner() = 'HR' or :ddl.get_base_table_owner() = 'HR')
    and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DBS1.NET' )

The `GET_BASE_TABLE_OWNER` member function is used in the DDL LCR rule because the `GET_OBJECT_OWNER` function can return `NULL` if a user who does not own an object performs a DDL change on the object.

Given these rules in the positive rule set for the propagation, the following list provides examples of changes propagated by the propagation:

- A row is inserted into the `hr.countries` table.

- The `hr.loc_city_ix` index is altered.

- The `hr.employees` table is truncated.

- A column is added to the `hr.countries` table.

- The `hr.update_job_history` trigger is altered.

- A new table named `candidates` is created in the `hr` schema.

- Twenty rows are inserted into the `hr.candidates` table.

The propagation propagates the LCRs that contain all of the changes previously listed from the source queue to the destination queue.

Now, given the same rules, suppose a row is inserted into the `oe.inventories` table. This change is ignored because the `oe` schema was not specified in a schema rule, and the `oe.inventories` table was not specified in a table rule.

### Table Rules

When you use a rule to specify a Streams task that is relevant only for an individual table, you are specifying a table rule. You can specify a table rule for DML changes, a table rule for DDL changes, or a table rule for each type of change to a specific table (two rules total).

A single table rule in the positive rule set for a capture process means that the capture process captures the results of either the DML changes or the DDL changes to the table. A single table rule in the negative rule set for a capture process means that the capture process discards the results of either the DML changes or the DDL changes to the table.

A single table rule in the positive rule set for a propagation means that the propagation propagates either the row LCRs or the DDL LCRs in the source queue that contain changes to the table. A single table rule in the negative rule set for a propagation means that the propagation discards either the row LCRs or the DDL LCRs in the source queue that contain changes to the table.

A single table rule in the positive rule set for an apply process means that the apply process applies either the row LCRs or the DDL LCRs in its queue that contain changes to the table. A single table rule in the negative rule set for an apply process means that the apply process discards either the row LCRs or the DDL LCRs in its queue that contain changes to the table.

#### Table Rules Example

Suppose you use the `ADD_TABLE_RULES` procedure in the `DBMS_STREAMS_ADM` package to instruct a Streams apply process to behave in the following ways:

- Apply All Row LCRs Related to the hr.locations Table

- Apply All DDL LCRs Related to the hr.countries Table

##### Apply All Row LCRs Related to the hr.locations Table

The changes in these row LCRs originated at the `dbs1.net` source database.

Run the `ADD_TABLE_RULES` procedure to create this rule:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name          =>  'hr.locations',
        streams_type        =>  'apply',
        streams_name        =>  'apply',
        queue_name          =>  'streams_queue',
        include_dml         =>  true,
        include_ddl         =>  false,
        include_tagged_lcr  =>  false,
        source_database     =>  'dbs1.net',
        inclusion_rule      =>  true);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rule is added to the positive rule set for the apply process.

The `ADD_TABLE_RULES` procedure creates a rule with a rule condition similar to the following:

    (((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'LOCATIONS'))
    and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )

##### Apply All DDL LCRs Related to the hr.countries Table

The changes in these DDL LCRs originated at the `dbs1.net` source database.

Run the `ADD_TABLE_RULES` procedure to create this rule:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name          =>  'hr.countries',
        streams_type        =>  'apply',
        streams_name        =>  'apply',
        queue_name          =>  'streams_queue',
        include_dml         =>  false,
        include_ddl         =>  true,
        include_tagged_lcr  =>  false,
        source_database     =>  'dbs1.net',
        inclusion_rule      =>  true);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rule is added to the positive rule set for the apply process.

The `ADD_TABLE_RULES` procedure creates a rule with a rule condition similar to the following:

    (((:ddl.get_object_owner() = 'HR' and :ddl.get_object_name() = 'COUNTRIES')
    or (:ddl.get_base_table_owner() = 'HR'
    and :ddl.get_base_table_name() = 'COUNTRIES')) and :ddl.is_null_tag() = 'Y'
    and :ddl.get_source_database_name() = 'DBS1.NET' )

The `GET_BASE_TABLE_OWNER` and `GET_BASE_TABLE_NAME` member functions are used in the DDL LCR rule because the `GET_OBJECT_OWNER` and `GET_OBJECT_NAME` functions can return `NULL` if a user who does not own an object performs a DDL change on the object.

##### Summary of Rules

In this example, the following table rules were defined:

- A table rule that evaluates to `TRUE` if a row LCR contains a row change that results from a DML operation on the `hr.locations` table.

- A table rule that evaluates to `TRUE` if a DDL LCR contains a DDL change performed on the `hr.countries` table.

Given these rules, the following list provides examples of changes applied by an apply process:

- A row is inserted into the `hr.locations` table.

- Five rows are deleted from the `hr.locations` table.

- A column is added to the `hr.countries` table.

The apply process dequeues the LCRs containing these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

- A row is inserted into the `hr.employees` table. This change is not applied because a change to the `hr.employees` table does not satisfy any of the rules.

- A row is updated in the `hr.countries` table. This change is a DML change, not a DDL change. This change is not applied because the rule on the `hr.countries` table is for DDL changes only.

- A column is added to the `hr.locations` table. This change is a DDL change, not a DML change. This change is not applied because the rule on the `hr.locations` table is for DML changes only.

### Subset Rules

A subset rule is a special type of table rule for DML changes that is relevant only to a subset of the rows in a table. You can create subset rules for capture processes, apply processes, and messaging clients using the `ADD_SUBSET_RULES` procedure, and you can create subset rules for propagations using the `ADD_SUBSET_PROPAGATION_RULES` procedure. These procedures enable you to use a condition similar to a `WHERE` clause in a `SELECT` statement to specify the following:

- That a capture process only captures a subset of the row changes resulting from DML changes to a particular table

- That a propagation only propagates a subset of the row LCRs relating to a particular table

- That an apply process only applies a subset of the row LCRs relating to a particular table

- That a messaging client only dequeues a subset of the row LCRs relating to a particular table

The `ADD_SUBSET_RULES` procedure and the `ADD_SUBSET_PROPAGATION_RULES` procedure can add subset rules to the positive rule set only of a Streams client. You cannot add subset rules to the negative rule set for a Streams client using these procedures.

The following sections describe subset rules in more detail:

- Subset Rules Example

- Row Migration and Subset Rules

- Subset Rules and Supplemental Logging

- Guidelines for Using Subset Rules

- Restrictions for Subset Rules

  Note: Capture process, propagation, and messaging client subset rules can be specified only at databases running Oracle Database 10g, but apply process subset rules can be specified at databases running Oracle9i Release 2 (9.2) or later.

#### Subset Rules Example

This example instructs a Streams apply process to apply a subset of row LCRs relating to the `hr.regions` table where the `region_id` is `2`. These changes originated at the `dbs1.net` source database.

Run the `ADD_SUBSET_RULES` procedure to create three rules:

    BEGIN
      DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
        table_name               =>  'hr.regions',
        dml_condition            =>  'region_id=2',
        streams_type             =>  'apply',
        streams_name             =>  'apply',
        queue_name               =>  'streams_queue',
        include_tagged_lcr       =>  false,
        source_database          =>  'dbs1.net');
    END;
    /

The `ADD_SUBSET_RULES` procedure creates three rules: one for `INSERT` operations, one for `UPDATE` operations, and one for `DELETE` operations.

Here is the rule condition used by the insert rule:

    :dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS'
    AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET'
    AND :dml.get_command_type() IN ('UPDATE','INSERT')
    AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL)
    AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)
    AND (:dml.get_command_type()='INSERT'
    OR ((:dml.get_value('OLD','"REGION_ID"') IS NOT NULL)
    AND (((:dml.get_value('OLD','"REGION_ID"').AccessNumber() IS NOT NULL)
    AND NOT (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2))
    OR ((:dml.get_value('OLD','"REGION_ID"').AccessNumber() IS NULL)
    AND NOT EXISTS (SELECT 1 FROM SYS.DUAL
    WHERE (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2))))))

Based on this rule condition, row LCRs are evaluated in the following ways:

- For an insert, if the new value in the row LCR for `region_id` is `2`, then the insert is applied.

- For an insert, if the new value in the row LCR for `region_id` is not `2` or is `NULL`, then the insert is filtered out.

- For an update, if the old value in the row LCR for `region_id` is not `2` or is `NULL` and the new value in the row LCR for `region_id` is `2`, then the update is converted into an insert and applied. This automatic conversion is called row migration. See "Row Migration and Subset Rules" for more information.

Here is the rule condition used by the update rule:

    :dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS'
    AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET'
    AND :dml.get_command_type()='UPDATE'
    AND (:dml.get_value('NEW','"REGION_ID"') IS NOT NULL)
    AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL)
    AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2)
    AND (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2)

Based on this rule condition, row LCRs are evaluated in the following ways:

- For an update, if both the old value and the new value in the row LCR for `region_id` are `2`, then the update is applied as an update.

- For an update, if either the old value or the new value in the row LCR for `region_id` is not `2` or is `NULL`, then the update does not satisfy the update rule. The LCR can satisfy the insert rule, the delete rule, or neither rule.

Here is the rule condition used by the delete rule:

    :dml.get_object_owner()='HR' AND :dml.get_object_name()='REGIONS'
    AND :dml.is_null_tag()='Y' AND :dml.get_source_database_name()='DBS1.NET'
    AND :dml.get_command_type() IN ('UPDATE','DELETE')
    AND (:dml.get_value('OLD','"REGION_ID"') IS NOT NULL)
    AND (:dml.get_value('OLD','"REGION_ID"').AccessNumber()=2)
    AND (:dml.get_command_type()='DELETE'
    OR ((:dml.get_value('NEW','"REGION_ID"') IS NOT NULL)
    AND (((:dml.get_value('NEW','"REGION_ID"').AccessNumber() IS NOT NULL)
    AND NOT (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2))
    OR ((:dml.get_value('NEW','"REGION_ID"').AccessNumber() IS NULL)
    AND NOT EXISTS (SELECT 1 FROM SYS.DUAL
    WHERE (:dml.get_value('NEW','"REGION_ID"').AccessNumber()=2))))))

Based on this rule condition, row LCRs are evaluated in the following ways:

- For a delete, if the old value in the row LCR for `region_id` is `2`, then the delete is applied.

- For a delete, if the old value in the row LCR for `region_id` is not `2` or is `NULL`, then the delete is filtered out.

- For an update, if the old value in the row LCR for `region_id` is `2` and the new value in the row LCR for `region_id` is not `2` or is `NULL`, then the update is converted into a delete and applied. This automatic conversion is called row migration. See "Row Migration and Subset Rules" for more information.

Given these subset rules, the following list provides examples of changes applied by an apply process:

- A row is updated in the `hr.regions` table where the old `region_id` is `4` and the new value of `region_id` is `2`. This update is transformed into an insert.

- A row is updated in the `hr.regions` table where the old `region_id` is `2` and the new value of `region_id` is `1`. This update is transformed into a delete.

The apply process dequeues row LCRs containing these changes from its associated queue and applies them to the `hr.regions` table at the destination database.

Given these subset rules, the following list provides examples of changes that are ignored by the apply process:

- A row is inserted into the `hr.employees` table. This change is not applied because a change to the `hr.employees` table does not satisfy the subset rules.

- A row is updated in the `hr.regions` table where the `region_id` was `1` before the update and remains `1` after the update. This change is not applied because the subset rules for the `hr.regions` table evaluate to `TRUE` only when the new or old (or both) values for `region_id` is `2`.

#### Row Migration and Subset Rules

When you use subset rules, an update operation can be converted into an insert or delete operation when it is captured, propagated, applied, or dequeued. This automatic conversion is called row migration and is performed by an internal transformation specified automatically in the action context for a subset rule. The following sections describe row migration during capture, propagation, apply, and dequeue.

Attention: Subset rules should reside only in positive rule sets. Do not add subset rules to negative rule sets. Doing so can have unpredictable results, because row migration would not be performed on LCRs that are not discarded by the negative rule set. Also, row migration is not performed on LCRs discarded because they evaluate to `TRUE` against a negative rule set.

##### Row Migration During Capture

When a subset rule is in the rule set for a capture process, an update that satisfies the subset rule can be converted into an insert or delete when it is captured.

For example, suppose you use a subset rule to specify that a capture process captures changes to the `hr.employees` table where the employee's `department_id` is `50` using the following subset condition: `department_id` `=` `50`. Assume that the table at the source database contains records for employees from all departments. If a DML operation changes an employee's `department_id` from `80` to `50`, then the capture process with the subset rule converts the update operation into an insert operation and captures the change. Therefore, a row LCR that contains an `INSERT` is enqueued into the capture process queue. Figure 6-2 illustrates this example.

Figure 6-2 Row Migration During Capture

Description of "Figure 6-2 Row Migration During Capture"

Similarly, if a captured update changes an employee's `department_id` from `50` to `20`, then a capture process with this subset rule converts the update operation into a `DELETE` operation.

##### Row Migration During Propagation

When a subset rule is in the rule set for a propagation, an update operation can be converted into an insert or delete operation when a row LCR is propagated.

For example, suppose you use a subset rule to specify that a propagation propagates changes to the `hr.employees` table where the employee's `department_id` is `50` using the following subset condition: `department_id` `=` `50`. If the source queue for the propagation contains a row LCR with an update operation on the `hr.employees` table that changes an employee's `department_id` from `50` to `80`, then the propagation with the subset rule converts the update operation into a delete operation and propagates the row LCR to the destination queue. Therefore, a row LCR that contains a `DELETE` is enqueued into the destination queue. Figure 6-3 illustrates this example.

Figure 6-3 Row Migration During Propagation

Description of "Figure 6-3 Row Migration During Propagation"

Similarly, if a captured update changes an employee's `department_id` from `80` to `50`, then a propagation with this subset rule converts the update operation into an `INSERT` operation.

##### Row Migration During Apply

When a subset rule is in the rule set for an apply process, an update operation can be converted into an insert or delete operation when a row LCR is applied.

For example, suppose you use a subset rule to specify that an apply process applies changes to the `hr.employees` table where the employee's `department_id` is `50` using the following subset condition: `department_id` `=` `50`. Assume that the table at the destination database is a subset table that only contains records for employees whose `department_id` is `50`. If a source database captures a change to an employee that changes the employee's `department_id` from `80` to `50`, then the apply process with the subset rule at a destination database applies this change by converting the update operation into an insert operation. This conversion is needed because the employee's row does not exist in the destination table. Figure 6-4 illustrates this example.

Figure 6-4 Row Migration During Apply

Description of "Figure 6-4 Row Migration During Apply"

Similarly, if a captured update changes an employee's `department_id` from `50` to `20`, then an apply process with this subset rule converts the update operation into a `DELETE` operation.

##### Row Migration During Dequeue by a Messaging Client

When a subset rule is in the rule set for a messaging client, an update operation can be converted into an insert or delete operation when a row LCR is dequeued.

For example, suppose you use a subset rule to specify that a messaging client dequeues changes to the `hr.employees` table when the employee's `department_id` is `50` using the following subset condition: `department_id` `=` `50`. If the queue for a messaging client contains a user-enqueued row LCR with an update operation on the `hr.employees` table that changes an employee's `department_id` from `50` to `90`, then when a user or application invokes a messaging client with this subset rule, the messaging client converts the update operation into a delete operation and dequeues the row LCR. Therefore, a row LCR that contains a `DELETE` is dequeued. The messaging client can process this row LCR in any customized way. For example, it can send the row LCR to a custom application. Figure 6-5 illustrates this example.

Figure 6-5 Row Migration During Dequeue by a Messaging Client

Description of "Figure 6-5 Row Migration During Dequeue by a Messaging Client"

Similarly, if a user-enqueued row LCR contains an update that changes an employee's `department_id` from `90` to `50`, then a messaging client with this subset rule converts the `UPDATE` operation into an `INSERT` operation during dequeue.

#### Subset Rules and Supplemental Logging

If you specify a subset rule for a table for capture, propagation, or apply, then an unconditional supplemental log group must be specified at the source database for all the columns in the subset condition and all of the columns in the table(s) at the destination database(s) that will apply these changes. In some cases, when a subset rule is specified, an update can be converted to an insert, and, in these cases, supplemental information might be needed for some or all of the columns.

For example, if you specify a subset rule for an apply process at database `dbs2.net` on the `postal_code` column in the `hr.locations` table, and the source database for changes to this table is `dbs1.net`, then specify supplemental logging at `dbs1.net` for all of the columns that exist in the `hr.locations` table at `dbs2.net`, as well as the `postal_code` column, even if this column does not exist in the table at the destination database.

See Also: Oracle Streams Replication Administrator's Guide for detailed information about supplemental logging

#### Guidelines for Using Subset Rules

The following sections provide guidelines for using subset rules:

- Use Capture Subset Rules When All Destinations Need Only a Subset of Changes

- Use Propagation or Apply Subset Rules When Some Destinations Need Subsets

- Make Sure the Table Where Subset Row LCRs Are Applied Is a Subset Table

##### Use Capture Subset Rules When All Destinations Need Only a Subset of Changes

Subset rules should be used with a capture process when all destination databases of the capture process need only row changes that satisfy the subset condition for the table. In this case, a capture process captures a subset of the DML changes to the table, and one or more propagations propagate these changes in the form of row LCRs to one or more destination databases. At each destination database, an apply process applies these row LCRs to a subset table in which all of the rows satisfy the subset condition in the subset rules for the capture process. None of the destination databases need all of the DML changes made to the table. When you use subset rules for a local capture process, some additional overhead is incurred to perform row migrations at the site running the source database.

##### Use Propagation or Apply Subset Rules When Some Destinations Need Subsets

Subset rules should be used with a propagation or an apply process when some destinations in an environment need only a subset of captured DML changes. The following are examples of such an environment:

- Most of the destination databases for captured DML changes to a table need a different subset of these changes.

- Most of the destination databases need all of the captured DML changes to a table, but some destination databases need only a subset of these changes.

In these types of environments, the capture process must capture all of the changes to the table, but you can use subset rules with propagations and apply processes to ensure that subset tables at destination databases only apply the correct subset of captured DML changes.

Consider these factors when you decide to use subset rules with a propagation in this type of environment:

- You can reduce network traffic because fewer row LCRs are propagated over the network.

- The site that contains the source queue for the propagation incurs some additional overhead to perform row migrations.

Consider these factors when you decide to use subset rules with an apply process in this type of environment:

- The queue used by the apply process can contain all row LCRs for the subset table. In a directed networks environment, propagations can propagate any of the row LCRs for the table to destination queues as appropriate, whether or not the apply process applies these row LCRs.

- The site that is running the apply process incurs some additional overhead to perform row migrations.

##### Make Sure the Table Where Subset Row LCRs Are Applied Is a Subset Table

If an apply process might apply row LCRs that have been transformed by a row migration, then Oracle recommends that the table at the destination database be a subset table where each row matches the condition in the subset rule. If the table is not such a subset table, then apply errors might result.

For example, consider a scenario in which a subset rule for a capture process has the condition `department_id` `=` `50` for DML changes to the `hr.employees` table. If the `hr.employees` table at a destination database of this capture process contains rows for employees in all departments, not just in department `50`, then a constraint violation might result during apply:

1. At the source database, a DML change updates the `hr.employees` table and changes the `department_id` for the employee with an `employee_id` of `100` from `90` to `50`.

2. A capture process using the subset rule captures the change and converts the update into an insert and enqueues the change into the capture process queue as a row LCR.

3. A propagation propagates the row LCR to the destination database without modifying it.

4. An apply process attempts to apply the row LCR as an insert at the destination database, but an employee with an `employee_id` of `100` already exists in the `hr.employees` table, and an apply error results.

In this case, if the table at the destination database were a subset of the `hr.employees` table and only contained rows of employees whose `department_id` was `50`, then the insert would have been applied successfully.

Similarly, if an apply process might apply row LCRs that have been transformed by a row migration to a table, and you allow users or applications to perform DML operations on the table, then Oracle recommends that all DML changes satisfy the subset condition. If you allow local changes to the table, then the apply process cannot ensure that all rows in the table meet the subset condition. For example, suppose the condition is `department_id` `=` `50` for the `hr.employees` table. If a user or an application inserts a row for an employee whose `department_id` is `30`, then this row remains in the table and is not removed by the apply process. Similarly, if a user or an application updates a row locally and changes the `department_id` to `30`, then this row also remains in the table.

#### Restrictions for Subset Rules

The following restrictions apply to subset rules:

- A table with the table name referenced in the subset rule must exist in the same database as the subset rule, and this table must be in the same schema referenced for the table in the subset rule.

- If the subset rule is in the positive rule set for a capture process, then the table must contain the columns specified in the subset condition, and the datatype of each of these columns must match the datatype of the corresponding column at the source database.

- If the subset rule is in the positive rule set for a propagation or apply process, then the table must contain the columns specified in the subset condition, and the datatype of each column must match the datatype of the corresponding column in row LCRs that evaluate to `TRUE` for the subset rule.

- Creating subset rules for tables that have one or more LOB, `LONG`, `LONG` `RAW`, or user-defined type columns is not supported.

### Message Rules

When you use a rule to specify a Streams task that is relevant only for a user-enqueued message of a specific message type, you are specifying a message rule. You can specify message rules for propagations, apply processes, and messaging clients.

A single message rule in the positive rule set for a propagation means that the propagation propagates the user-enqueued messages of the message type in the source queue that satisfy the rule condition. A single message rule in the negative rule set for a propagation means that the propagation discards the user-enqueued messages of the message type in the source queue that satisfy the rule condition.

A single message rule in the positive rule set for an apply process means that the apply process dequeues user-enqueued messages of the message type that satisfy the rule condition. The apply process then sends these user-enqueued messages to its message handler. A single message rule in the negative rule set for an apply process means that the apply process discards user-enqueued messages of the message type in its queue that satisfy the rule condition.

A single message rule in the positive rule set for a messaging client means that a user or an application can use the messaging client to dequeue user-enqueued messages of the message type that satisfy the rule condition. A single message rule in the negative rule set for a messaging client means that the messaging client discards user-enqueued messages of the message type in its queue that satisfy the rule condition. Unlike propagations and apply processes, which propagate or apply messages automatically when they are running, a messaging client does not automatically dequeue or discard messages. Instead, a messaging client must be invoked by a user or application to dequeue or discard messages.

#### Message Rule Example

Suppose you use the `ADD_MESSAGE_RULE` procedure in the `DBMS_STREAMS_ADM` package to instruct a Streams client to behave in the following ways:

- Dequeue User-Enqueued Messages If region Is EUROPE and priority Is 1

- Send User-Enqueued Messages to a Message Handler If region Is AMERICAS and priority Is 2

The first instruction in the previous list pertains to a messaging client, while the second instruction pertains to an apply process.

The rules created in these examples are for messages of the following type:

    CREATE TYPE strmadmin.region_pri_msg AS OBJECT(
      region         VARCHAR2(100),
      priority       NUMBER,
      message        VARCHAR2(3000))
    /

##### Dequeue User-Enqueued Messages If region Is EUROPE and priority Is 1

Run the `ADD_MESSAGE_RULE` procedure to create a rule for messages of `region_pri_msg` type:

    BEGIN
      DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
        message_type    =>  'strmadmin.region_pri_msg',
        rule_condition  =>  ':msg.region = ''EUROPE'' AND  ' ||
                            ':msg.priority = ''1'' ',
        streams_type    =>  'dequeue',
        streams_name    =>  'msg_client',
        queue_name      =>  'streams_queue',
        inclusion_rule  =>  true);
    END;
    /

Notice that `dequeue` is specified for the `streams_type` parameter. Therefore, this procedure creates a messaging client named `msg_client` if it does not already exist. If this messaging client already exists, then this procedure adds the message rule to its rule set. Also, notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rule is added to the positive rule set for the messaging client. The user who runs this procedure is granted the privileges to dequeue from the queue using the messaging client.

The `ADD_MESSAGE_RULE` procedure creates a rule with a rule condition similar to the following:

    :"VAR$_52".region = 'EUROPE' AND  :"VAR$_52".priority = '1'

The variables in the rule condition that begin with `VAR$` are variables that are specified in the system-generated evaluation context for the rule.

See Also: "Evaluation Contexts Used in Streams"

##### Send User-Enqueued Messages to a Message Handler If region Is AMERICAS and priority Is 2

Run the `ADD_MESSAGE_RULE` procedure to create a rule for messages of `region_pri_msg` type:

    BEGIN
      DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
        message_type    =>  'strmadmin.region_pri_msg',
        rule_condition  =>  ':msg.region = ''AMERICAS'' AND  ' ||
                            ':msg.priority = ''2'' ',
        streams_type    =>  'apply',
        streams_name    =>  'apply_msg',
        queue_name      =>  'streams_queue',
        inclusion_rule  =>  true);
    END;
    /

Notice that `apply` is specified for the `streams_type` parameter. Therefore, this procedure creates an apply process named `apply_msg` if it does not already exist. If this apply process already exists, then this procedure adds the message rule to its rule set. Also, notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rule is added to the positive rule set for the messaging client.

The `ADD_MESSAGE_RULE` procedure creates a rule with a rule condition similar to the following:

    :"VAR$_56".region = 'AMERICAS' AND  :"VAR$_56".priority = '2'

The variables in the rule condition that begin with `VAR$` are variables that are specified in the system-generated evaluation context for the rule.

See Also: "Evaluation Contexts Used in Streams"

##### Summary of Rules

In this example, the following message rules were defined:

- A message rule for a messaging client named `msg_client` that evaluates to `TRUE` if a message has `EUROPE` for its region and `1` for its priority. Given this rule, a user or application can use the messaging client to dequeue messages of `region_pri_msg` type that satisfy the rule condition.

- A message rule for an apply process named `apply_msg` that evaluates to `TRUE` if a message has `AMERICAS` for its region and `2` for its priority. Given this rule, the apply process dequeues messages of `region_pri_msg` type that satisfy the rule condition and sends these messages to its message handler or reenqueues the messages into a specified queue.

  See Also: "Non-LCR User Message Processing" "Enqueue Destinations for Messages During Apply"

### System-Created Rules and Negative Rule Sets

You add system-created rules to a negative rule set to specify that you do not want a Streams client to perform its task for changes that satisfy these rules. Specifically, a system-created rule in a negative rule set means the following for each type of Streams client:

- A capture process discards changes that satisfy the rule.

- A propagation discards messages in its source queue that satisfy the rule.

- An apply process discards messages in its queue that satisfy the rule.

- A messaging client discards messages in its queue that satisfy the rule.

If a Streams client does not have a negative rule set, then you can create a negative rule set and add rules to it by running one of the following procedures and setting the `inclusion_rule` parameter to `false`:

- `DBMS_STREAMS_ADM.ADD_TABLE_RULES`

- `DBMS_STREAMS_ADM.ADD_SCHEMA_RULES`

- `DBMS_STREAMS_ADM.ADD_GLOBAL_RULES`

- `DBMS_STREAMS_ADM.ADD_MESSAGE_RULE`

- `DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES`

- `DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES`

- `DBMS_STREAMS_ADM.ADD_GLOBAL_PROPAGATION_RULES`

- `DBMS_STREAMS_ADM.ADD_MESSAGE_PROPAGATION_RULE`

If a negative rule set already exists for the Streams client when you run one of these procedures, then the procedure adds the system-created rules to the existing negative rule set.

Alternatively, you can create a negative rule set when you create a Streams client by running one of the following procedures and specifying a non-`NULL` value for the `negative_rule_set_name` parameter:

- `DBMS_CAPTURE_ADM.CREATE_CAPTURE`

- `DBMS_PROPAGATION_ADM.CREATE_PROPAGATION`

- `DBMS_APPLY_ADM.CREATE_APPLY`

Also, you can specify a negative rule set for an existing Streams client by altering the client. For example, to specify a negative rule set for an existing capture process, use the `DBMS_CAPTURE_ADM.ALTER_CAPTURE` procedure. After a Streams client has a negative rule set, you can use the procedures in the `DBMS_STREAM_ADM` package listed previously to add system-created rules to it.

Instead of adding rules to a negative rule set, you can also exclude changes to certain tables or schemas in the following ways:

- Do not add system-created rules for the table or schema to a positive rule set for a Streams client. For example, to capture DML changes to all of the tables in a particular schema except for one table, add a DML table rule for each table in the schema, except for the excluded table, to the positive rule set for the capture process. The disadvantages of this approach are that there can be many tables in a schema and each one requires a separate DML rule, and, if a new table is added to the schema, and you want to capture changes to this new table, then a new DML rule must be added for this table to the positive rule set for the capture process.

- Use the `NOT` logical condition in the rule condition of a complex rule in the positive rule set for a Streams client. For example, to capture DML changes to all of the tables in a particular schema except for one table, use the `DBMS_STREAMS_ADM.ADD_SCHEMA_RULES` procedure to add a system-created DML schema rule to the positive rule set for the capture process that instructs the capture process to capture changes to the schema, and use the `and_condition` parameter to exclude the table with the `NOT` logical condition. The disadvantages to this approach are that it involves manually specifying parts of rule conditions, which can be error prone, and rule evaluation is not as efficient for complex rules as it is for unmodified system-created rules.

Given the goal of capturing DML changes to all of the tables in a particular schema except for one table, you can add a DML schema rule to the positive rule set for the capture process and a DML table rule for the excluded table to the negative rule set for the capture process.

This approach has the following advantages over the alternatives described previously:

- You add only two rules to achieve the goal.

- If a new table is added to the schema, and you want to capture DML changes to the table, then the capture process captures these changes without requiring modifications to existing rules or additions of new rules.

- You do not need to specify or edit rule conditions manually.

- Rule evaluation is more efficient because you avoid using complex rules.

See Also: "Complex Rule Conditions" "System-Created Rules with Added User-Defined Conditions" for more information about the `and_condition` parameter

#### Negative Rule Set Example

Suppose you want to apply row LCRs that contain the results of DML changes to all of the tables in `hr` schema except for the `job_history` table. To do so, you can use the `ADD_SCHEMA_RULES` procedure in the `DBMS_STREAMS_ADM` package to instruct a Streams apply process to apply row LCRs that contain the results of DML changes to the tables in the `hr` schema. In this case, the procedure creates a schema rule and adds the rule to the positive rule set for the apply process.

You can use the `ADD_TABLE_RULES` procedure in the `DBMS_STREAMS_ADM` package to instruct the Streams apply process to discard row LCRs that contain the results of DML changes to the tables in the `hr.job_history` table. In this case, the procedure creates a table rule and adds the rule to the negative rule set for the apply process.

The following sections explain how to run these procedures:

- Apply All DML Changes to the Tables in the hr Schema

- Discard Row LCRs Containing DML Changes to the hr.job_history Table

##### Apply All DML Changes to the Tables in the hr Schema

These changes originated at the `dbs1.net` source database.

Run the `ADD_SCHEMA_RULES` procedure to create this rule:

    BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name         =>  'hr',
        streams_type        =>  'apply',
        streams_name        =>  'apply',
        queue_name          =>  'streams_queue',
        include_dml         =>  true,
        include_ddl         =>  false,
        include_tagged_lcr  =>  false,
        source_database     =>  'dbs1.net',
        inclusion_rule      =>  true);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `true`. This setting means that the system-created rule is added to the positive rule set for the apply process.

The `ADD_SCHEMA_RULES` procedure creates a rule with a rule condition similar to the following:

    ((:dml.get_object_owner() = 'HR') and :dml.is_null_tag() = 'Y'
    and :dml.get_source_database_name() = 'DBS1.NET' )

##### Discard Row LCRs Containing DML Changes to the hr.job_history Table

These changes originated at the `dbs1.net` source database.

Run the `ADD_TABLE_RULES` procedure to create this rule:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name          =>  'hr.job_history',
        streams_type        =>  'apply',
        streams_name        =>  'apply',
        queue_name          =>  'streams_queue',
        include_dml         =>  true,
        include_ddl         =>  false,
        include_tagged_lcr  =>  true,
        source_database     =>  'dbs1.net',
        inclusion_rule      =>  false);
    END;
    /

Notice that the `inclusion_rule` parameter is set to `false`. This setting means that the system-created rule is added to the negative rule set for the apply process.

Also notice that the `include_tagged_lcr` parameter is set to `true`. This setting means that all changes for the table, including tagged LCRs that satisfy all of the other rule conditions, will be discarded. In most cases, specify `true` for the `include_tagged_lcr` parameter if the `inclusion_rule` parameter is set to `false`.

The `ADD_TABLE_RULES` procedure creates a rule with a rule condition similar to the following:

    (((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'JOB_HISTORY'))
    and :dml.get_source_database_name() = 'DBS1.NET' )

##### Summary of Rules

In this example, the following rules were defined:

- A schema rule that evaluates to `TRUE` if a DML operation is performed on the tables in the `hr` schema. This rule is in the positive rule set for the apply process.

- A table rule that evaluates to `TRUE` if a DML operation is performed on the `hr.job_history` table. This rule is in the negative rule set for the apply process.

Given these rules, the following list provides examples of changes applied by the apply process:

- A row is inserted into the `hr.departments` table.

- Five rows are updated in the `hr.employees` table.

- A row is deleted from the `hr.countries` table.

The apply process dequeues these changes from its associated queue and applies them to the database objects at the destination database.

Given these rules, the following list provides examples of changes that are ignored by the apply process:

- A row is inserted into the `hr.job_history` table.

- A row is updated in the `hr.job_history` table.

- A row is deleted from the `hr.job_history` table.

These changes are not applied because they satisfy a rule in the negative rule set for the apply process.

See Also: "Rule Sets and Rule Evaluation of Messages"

### System-Created Rules with Added User-Defined Conditions

Some of the procedures that create rules in the `DBMS_STREAMS_ADM` package include an `and_condition` parameter. This parameter enables you to add conditions to system-created rules. The condition specified by the `and_condition` parameter is appended to the system-created rule condition using an `AND` clause in the following way:

    (system_condition) AND (and_condition)

The variable in the specified condition must be `:lcr`. For example, to specify that the table rules generated by the `ADD_TABLE_RULES` procedure evaluate to `TRUE` only if the table is `hr.departments`, the source database is `dbs 1.net`, and the Streams tag is the hexadecimal equivalent of `'02'`, run the following procedure:

    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name          =>  'hr.departments',
        streams_type        =>  'apply',
        streams_name        =>  'apply_02',
        queue_name          =>  'streams_queue',
        include_dml         =>  true,
        include_ddl         =>  true,
        include_tagged_lcr  =>  true,
        source_database     =>  'dbs1.net',
        inclusion_rule      =>  true,
        and_condition       =>  ':lcr.get_tag() = HEXTORAW(''02'')');
    END;
    /

The `ADD_TABLE_RULES` procedure creates a DML rule with the following condition:

    (((((:dml.get_object_owner() = 'HR' and :dml.get_object_name() = 'DEPARTMENTS'))
     and :dml.get_source_database_name() = 'DBS1.NET' ))
    and (:dml.get_tag() = HEXTORAW('02')))

It creates a DDL rule with the following condition:

    (((((:ddl.get_object_owner() = 'HR' and :ddl.get_object_name() = 'DEPARTMENTS')
    or (:ddl.get_base_table_owner() = 'HR'
    and :ddl.get_base_table_name() = 'DEPARTMENTS'))
    and :ddl.get_source_database_name() = 'DBS1.NET' ))
    and (:ddl.get_tag() = HEXTORAW('02')))

Notice that the `:lcr` in the specified condition is converted to `:dml` or `:ddl`, depending on the rule that is being generated. If you are specifying an LCR member subprogram that is dependent on the LCR type (row or DDL), then make sure this procedure only generates the appropriate rule. Specifically, if you specify an LCR member subprogram that is valid only for row LCRs, then specify `true` for the `include_dml` parameter and `false` for the `include_ddl` parameter. If you specify an LCR member subprogram that is valid only for DDL LCRs, then specify `false` for the `include_dml` parameter and `true` for the `include_ddl` parameter.

For example, the `GET_OBJECT_TYPE` member function only applies to DDL LCRs. Therefore, if you use this member function in an `and_condition`, then specify `false` for the `include_dml` parameter and `true` for the `include_ddl` parameter.

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about LCR member subprograms Oracle Streams Replication Administrator's Guide for more information about Streams tags

## Evaluation Contexts Used in Streams

The following sections describe the system-created evaluation contexts used in Streams.

- Evaluation Context for Global, Schema, Table, and Subset Rules

- Evaluation Contexts for Message Rules

### Evaluation Context for Global, Schema, Table, and Subset Rules

When you create global, schema, table, and subset rules, the system-created rule sets and rules use a built-in evaluation context in the `SYS` schema named `STREAMS$_EVALUATION_CONTEXT`. `PUBLIC` is granted the `EXECUTE` privilege on this evaluation context. Global, schema, table, and subset rules can be used by capture processes, propagations, apply processes, and messaging clients.

During Oracle installation, the following statement creates the Streams evaluation context:

    DECLARE
      vt  SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      vt := SYS.RE$VARIABLE_TYPE_LIST(
        SYS.RE$VARIABLE_TYPE('DML', 'SYS.LCR$_ROW_RECORD',
           'SYS.DBMS_STREAMS_INTERNAL.ROW_VARIABLE_VALUE_FUNCTION',
           'SYS.DBMS_STREAMS_INTERNAL.ROW_FAST_EVALUATION_FUNCTION'),
        SYS.RE$VARIABLE_TYPE('DDL', 'SYS.LCR$_DDL_RECORD',
           'SYS.DBMS_STREAMS_INTERNAL.DDL_VARIABLE_VALUE_FUNCTION',
           'SYS.DBMS_STREAMS_INTERNAL.DDL_FAST_EVALUATION_FUNCTION'));
        SYS.RE$VARIABLE_TYPE(NULL, 'SYS.ANYDATA',
           NULL,
           'SYS.DBMS_STREAMS_INTERNAL.ANYDATA_FAST_EVAL_FUNCTION'));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
        variable_types          => vt,
        evaluation_function     =>
                           'SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION');
    END;
    /

This statement includes references to the following internal functions in the `SYS.DBMS_STREAM_INTERNAL` package:

- `ROW_VARIABLE_VALUE_FUNCTION`

- `DDL_VARIABLE_VALUE_FUNCTION`

- `EVALUATION_CONTEXT_FUNCTION`

- `ROW_FAST_EVALUATION_FUNCTION`

- `DDL_FAST_EVALUATION_FUNCTION`

- `ANYDATA_FAST_EVAL_FUNCTION`

Attention: Information about these internal functions is provided for reference purposes only. You should never run any of these functions directly.

The `ROW_VARIABLE_VALUE_FUNCTION` converts an `ANYDATA` payload, which encapsulates a `SYS.LCR$_ROW_RECORD` instance, into a `SYS.LCR$_ROW_RECORD` instance prior to evaluating rules on the data.

The `DDL_VARIABLE_VALUE_FUNCTION` converts an `ANYDATA` payload, which encapsulates a `SYS.LCR$_DDL_RECORD` instance, into a `SYS.LCR$_DDL_RECORD` instance prior to evaluating rules on the data.

The `EVALUATION_CONTEXT_FUNCTION` is specified as an `evaluation_function` in the call to the `CREATE_EVALUATION_CONTEXT` procedure. This function supplements normal rule evaluation for captured messages. A capture process enqueues row LCRs and DDL LCRs into its queue, and this function enables it to enqueue other internal messages into the queue, such as commits, rollbacks, and data dictionary changes. This information is also used during rule evaluation for a propagation or apply process.

`ROW_FAST_EVALUATION_FUNCTION` improves performance by optimizing access to the following `LCR$_ROW_RECORD` member functions during rule evaluation:

- `GET_OBJECT_OWNER`

- `GET_OBJECT_NAME`

- `IS_NULL_TAG`

- `GET_SOURCE_DATABASE_NAME`

- `GET_COMMAND_TYPE`

`DDL_FAST_EVALUATION_FUNCTION` improves performance by optimizing access to the following `LCR$_DDL_RECORD` member functions during rule evaluation if the condition is `<`, `<=`, `=`, `>=`, or `>` and the other operand is a constant:

- `GET_OBJECT_OWNER`

- `GET_OBJECT_NAME`

- `IS_NULL_TAG`

- `GET_SOURCE_DATABASE_NAME`

- `GET_COMMAND_TYPE`

- `GET_BASE_TABLE_NAME`

- `GET_BASE_TABLE_OWNER`

`ANYDATA_FAST_EVAL_FUNCTION` improves performance by optimizing access to values inside an `ANYDATA` object.

Rules created using the `DBMS_STREAMS_ADM` package use `ROW_FAST_EVALUATION_FUNCTION` or `DDL_FAST_EVALUATION_FUNCTION`, except for subset rules created using the `ADD_SUBSET_RULES` or `ADD_SUBSET_PROPAGATION_RULES` procedure.

See Also: Oracle Database PL/SQL Packages and Types Reference for more information about LCRs and their member functions

### Evaluation Contexts for Message Rules

When you use either the `ADD_MESSAGE_RULE` procedure or the `ADD_MESSAGE_PROPAGATION_RULE` procedure to create a message rule, the message rule uses a user-defined message type that you specify when you create the rule. Such a system-created message rule uses a system-created evaluation context. The name of the system-created evaluation context is different for each message type used to create message rules. Such an evaluation context has a system-generated name and is created in the schema that owns the rule. Only the user who owns this evaluation context is granted the `EXECUTE` privilege on it.

The evaluation context for this type of message rule contains a variable that is the same type as the message type. The name of this variable is in the form `VAR$_`number, where number is a system-generated number. For example, if you specify `strmadmin.region_pri_msg` as the message type when you create a message rule, then the system-created evaluation context has a variable of this type, and the variable is used in the rule condition. Assume that the following statement created the `strmadmin.region_pri_msg` type:

    CREATE TYPE strmadmin.region_pri_msg AS OBJECT(
      region         VARCHAR2(100),
      priority       NUMBER,
      message        VARCHAR2(3000))
    /

When you create a message rule using this type, you can specify the following rule condition:

    :msg.region = 'EUROPE' AND :msg.priority = '1'

The system-created message rule replaces `:msg` in the rule condition you specify with the name of the variable. The following is an example of a message rule condition that might result:

    :VAR$_52.region = 'EUROPE' AND  :VAR$_52.priority = '1'

In this case, `VAR$_52` is the variable name, the type of the `VAR$_52` variable is `strmadmin.region_pri_msg`, and the evaluation context for the rule contains this variable.

The message rule itself has an evaluation context. A statement similar to the following creates an evaluation context for a message rule:

    DECLARE
      vt  SYS.RE$VARIABLE_TYPE_LIST;
    BEGIN
      vt := SYS.RE$VARIABLE_TYPE_LIST(
        SYS.RE$VARIABLE_TYPE('VAR$_52', 'STRMADMIN.REGION_PRI_MSG',
           'SYS.DBMS_STREAMS_INTERNAL.MSG_VARIABLE_VALUE_FUNCTION', NULL));
      DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT(
        evaluation_context_name => 'STRMADMIN.EVAL_CTX$_99',
        variable_types          => vt,
        evaluation_function     => NULL);
    END;
    /

The name of the evaluation context is in the form `EVAL_CTX$_`number, where number is a system-generated number. In this example, the name of the evaluation context is `EVAL_CTX$_99`.

This statement also includes a reference to the `MSG_VARIABLE_VALUE_FUNCTION` internal function in the `SYS.DBMS_STREAM_INTERNAL` package. This function converts an `ANYDATA` payload, which encapsulates a message instance, into an instance of the same type as the variable prior to evaluating rules on the data. For example, if the variable type is `strmadmin.region_pri_msg`, then the `MSG_VARIABLE_VALUE_FUNCTION` converts the message payload from an `ANYDATA` payload to a `strmadmin.region_pri_msg` payload.

If you create rules for different message types, then Oracle creates a different evaluation context for each message type. If you create a new rule with the same message type as an existing rule, then the new rule uses the evaluation context for the existing rule. When you use the `ADD_MESSAGE_RULE` or `ADD_MESSAGE_PROPAGATION_RULE` to create a rule set for a messaging client or apply process, the new rule set does not have an evaluation context.

See Also: "Message Rules" "Evaluation Context for Global, Schema, Table, and Subset Rules"

## Streams and Event Contexts

In Streams, capture processes and messaging clients do not use event contexts, but propagations and apply processes do. Both captured messages and user-enqueued messages can be staged in a queue. When a message is staged in a queue, a propagation or apply process can send the message, along with an event context, to the rules engine for evaluation. An event context always has the following name-value pair: `AQ$_MESSAGE` as the name and the message as the value.

If you create a custom evaluation context, then you can create propagation and apply process rules that refer to Streams events using implicit variables. The variable value function for each implicit variable can check for event contexts with the name `AQ$_MESSAGE`. If an event context with this name is found, then the variable value function returns a value based on a message. You can also pass the event context to an evaluation function and a variable method function.

See Also: "Rule Set Evaluation" for more information about event contexts "Explicit and Implicit Variables" for more information about variable value functions "Evaluation Function"

## Streams and Action Contexts

The following sections describe the purposes of action contexts in Streams and the importance of ensuring that only one rule in a rule set can evaluate to `TRUE` for a particular rule condition.

### Purposes of Action Contexts in Streams

In Streams, an action context serves the following purposes:

- Internal LCR Transformations in Subset Rules

- Information About Declarative Rule-Based Transformations

- Custom Rule-Based Transformations

- Enqueue Destinations for Messages During Apply

- Execution Directives for Messages During Apply

A different name-value pair can exist in the action context of a rule for each of these purposes. If an action context for a rule contains more than one of these name-value pairs, then the actions specified or described by the name-value pairs are performed in the following order:

1. Perform subset transformation.

2. Display information about declarative rule-based transformation.

3. Perform custom rule-based transformation.

4. Follow execution directive and perform execution if directed to do so (apply only).

5. Enqueue into a destination queue (apply only).

   Note: The actions specified in the action context for a rule are performed only if the rule is in the positive rule set for a capture process, propagation, apply process, or messaging client. If a rule is in a negative rule set, then these Streams clients ignore the action context of the rule.

#### Internal LCR Transformations in Subset Rules

When you use subset rules, an update operation can be converted into an insert or delete operation when it is captured, propagated, applied, or dequeued. This automatic conversion is called row migration and is performed by an internal transformation specified in the action context when the subset rule evaluates to `TRUE`. The name-value pair for a subset transformation has `STREAMS$_ROW_SUBSET` for the name and either `INSERT` or `DELETE` for the value.

See Also: "Subset Rules" Chapter 15, "Managing Rule-Based Transformations" for information about using rule-based transformation with subset rules

#### Information About Declarative Rule-Based Transformations

A declarative rule-based transformation is an internal modification of a row LCR that results when a rule evaluates to `TRUE`. The name-value pair for a declarative rule-based transformation has `STREAMS$_INTERNAL_TRANFORM` for the name and the name of a data dictionary view that provides additional information about the transformation for the value.

The name-value pair added for a declarative rule-based transformation is for information purposes only. These name-value pairs are not used by Streams clients. However, the declarative rule-based transformations described in an action context are performed internally before any custom rule-based transformations specified in the same action context.

See Also: "Declarative Rule-Based Transformations" "Managing Declarative Rule-Based Transformations"

#### Custom Rule-Based Transformations

A custom rule-based transformation is any modification made by a user-defined function to a message when a rule evaluates to `TRUE`. The name-value pair for a custom rule-based transformation has `STREAMS$_TRANSFORM_FUNCTION` for the name and the name of the transformation function for the value.

See Also: "Custom Rule-Based Transformations" "Managing Custom Rule-Based Transformations"

#### Execution Directives for Messages During Apply

The `SET_EXECUTE` procedure in the `DBMS_APPLY_ADM` package specifies whether a message that satisfies the specified rule is executed by an apply process. The name-value pair for an execution directive has `APPLY$_EXECUTE` for the name and `NO` for the value if the apply process should not execute the message. If a message that satisfies a rule should be executed by an apply process, then this name-value pair is not present in the action context of the rule.

See Also: "Specifying Execute Directives for Apply Processes"

#### Enqueue Destinations for Messages During Apply

The `SET_ENQUEUE_DESTINATION` procedure in the `DBMS_APPLY_ADM` package sets the queue where a message that satisfies the specified rule is enqueued automatically by an apply process. The name-value pair for an enqueue destination has `APPLY$_ENQUEUE` for the name and the name of the destination queue for the value.

See Also: "Specifying Message Enqueues by Apply Processes"

### Make Sure Only One Rule Can Evaluate to TRUE for a Particular Rule Condition

If you use a non-`NULL` action context for one or more rules in a positive rule set, then make sure only one rule can evaluate to `TRUE` for a particular rule condition. If more than one rule evaluates to `TRUE` for a particular condition, then only one of the rules is returned, which can lead to unpredictable results.

For example, suppose two rules evaluate to `TRUE` if an LCR contains a DML change to the `hr.employees` table. The first rule has a `NULL` action context. The second rule has an action context that specifies a custom rule-based transformation. If there is a DML change to the `hr.employees` table, then both rules evaluate to `TRUE` for the change, but only one rule is returned. In this case, the transformation might or might not occur, depending on which rule is returned.

You might want to ensure that only one rule in a positive rule set can evaluate to `TRUE` for any condition, regardless of whether any of the rules have a non-`NULL` action context. By following this guideline, you can avoid unpredictable results if, for example, a non-`NULL` action context is added to a rule in the future.

See Also: Chapter 7, "Rule-Based Transformations"

### Action Context Considerations for Schema and Global Rules

If you use an action context for a custom rule-based transformation, enqueue destination, or execute directive with a schema rule or global rule, then the action specified by the action context is carried out on a message if the message causes the schema or global rule to evaluate to `TRUE`. For example, if a schema rule has an action context that specifies a custom rule-based transformation, then the transformation is performed on LCRs for the tables in the schema.

You might want to use an action context with a schema or global rule but exclude a subset of LCRs from the action performed by the action context. For example, if you want to perform a custom rule-based transformation on all of the tables in the `hr` schema except for the `job_history` table, then make sure the transformation function returns the original LCR if the table is `job_history`.

If you want to set an enqueue destination or an execute directive for all of the tables in the `hr` schema except for the `job_history` table, then you can use a schema rule and add the following condition to it:

    :dml.get_object_name() != 'JOB_HISTORY'

In this case, if you want LCRs for the `job_history` table to evaluate to `TRUE`, but you do not want to perform the enqueue or execute directive, then you can add a table rule for the table to a positive rule set. That is, the schema rule would have the enqueue destination or execute directive, but the table rule would not.

See Also: "System-Created Rules" for more information about schema and global rules

## User-Created Rules, Rule Sets, and Evaluation Contexts

The `DBMS_STREAMS_ADM` package generates system-created rules and rule sets, and it can specify an Oracle supplied evaluation context for rules and rule sets or generate system-created evaluation contexts. If you need to create rules, rule sets, or evaluation contexts that cannot be created using the `DBMS_STREAMS_ADM` package, then you can use the `DBMS_RULE_ADM` package to create them.

Use the `DBMS_RULE_ADM` package for the following reasons:

- You need to create rules with rule conditions that cannot be created using the `DBMS_STREAMS_ADM` package, such as rule conditions for specific types of operations, or rule conditions that use the `LIKE` condition.

- You need to create custom evaluation contexts for the rules in your Streams environment.

You can create a rule set using the `DBMS_RULE_ADM` package, and you can associate it with a capture process, propagation, apply process, or messaging client. Such a rule set can be a positive rule set or negative rule set for a Streams client, and a rule set can be a positive rule set for one Streams client and a negative rule set for another.

This section contains the following topics:

- User-Created Rules and Rule Sets

- User-Created Evaluation Contexts

  See Also: "Specifying a Rule Set for a Capture Process" "Specifying the Rule Set for a Propagation" "Specifying the Rule Set for an Apply Process"

### User-Created Rules and Rule Sets

The following sections describe some of the types of rules and rule sets that you can create using the `DBMS_RULE_ADM` package:

- Rule Conditions for Specific Types of Operations

- Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs

- Complex Rule Conditions

- Rule Conditions with Undefined Variables that Evaluate to NULL

- Variables as Function Parameters in Rule Conditions

  Note: You can add user-defined conditions to a system-created rule by using the `and_condition` parameter that is available in some of the procedures in the `DBMS_STREAMS_ADM` package. Using the `and_condition` parameter is sometimes easier than creating rules with the `DBMS_RULE_ADM` package.

  See Also: "System-Created Rules with Added User-Defined Conditions" for more information about the `and_condition` parameter

#### Rule Conditions for Specific Types of Operations

In some cases, you might want to capture, propagate, apply, or dequeue only changes that contain specific types of operations. For example, you might want to apply changes containing only insert operations for a particular table, but not other operations, such as update and delete.

Suppose you want to specify a rule condition that evaluates to `TRUE` only for `INSERT` operations on the `hr.employees` table. You can accomplish this by specifying the `INSERT` command type in the rule condition:

    :dml.get_command_type() = 'INSERT' AND :dml.get_object_owner() = 'HR'
    AND :dml.get_object_name() = 'EMPLOYEES' AND :dml.is_null_tag() = 'Y'

Similarly, suppose you want to specify a rule condition that evaluates to `TRUE` for all DML operations on the `hr.departments` table, except `DELETE` operations. You can accomplish this by specifying the following rule condition:

    :dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
    :dml.is_null_tag() = 'Y' AND (:dml.get_command_type() = 'INSERT' OR
    :dml.get_command_type() = 'UPDATE')

This rule condition evaluates to `TRUE` for `INSERT` and `UPDATE` operations on the `hr.departments` table, but not for `DELETE` operations. Because the `hr.departments` table does not include any LOB columns, you do not need to specify the LOB command types for DML operations (`LOB` `ERASE`, `LOB` `WRITE`, and `LOB` `TRIM`), but these command types should be specified in such a rule condition for a table that contains one or more LOB columns.

The following rule condition accomplishes the same behavior for the `hr.departments` table. That is, the following rule condition evaluates to `TRUE` for all DML operations on the `hr.departments` table, except `DELETE` operations:

    :dml.get_object_owner() = 'HR' AND :dml.get_object_name() = 'DEPARTMENTS' AND
    :dml.is_null_tag() = 'Y' AND :dml.get_command_type() != 'DELETE'

The example rule conditions described previously in this section are all simple rule conditions. However, when you add custom conditions to system-created rule conditions, the entire condition might not be a simple rule condition, and nonsimple rules might not evaluate efficiently. In general, you should use simple rule conditions whenever possible to improve rule evaluation performance. Rule conditions created using the `DBMS_STREAMS_ADM` package, without custom conditions added, are always simple.

See Also: "Simple Rule Conditions" "Complex Rule Conditions"

#### Rule Conditions that Instruct Streams Clients to Discard Unsupported LCRs

You can use the following functions in rule conditions to instruct a Streams client to discard LCRs that encapsulate unsupported changes:

- The `GET_COMPATIBLE` member function for LCRs. This function returns the minimal database compatibility required to support an LCR.

- The `COMPATIBLE_9_2` function, `COMPATIBLE_10_1` function, and `COMPATIBLE_10_2` function in the `DBMS_STREAMS` package. These functions return constant values that correspond to 9.2.0, 10.1.0, and 10.2.0 compatibility in a database, respectively. You control the compatibility of an Oracle database using the `COMPATIBLE` initialization parameter.

For example, consider the following rule:

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'strmadmin.dml_compat_9_2',
        condition => ':dml.GET_COMPATIBLE() > DBMS_STREAMS.COMPATIBLE_9_2()');
    END;
    /

If this rule is in the negative rule set for a Streams client, such as a capture process, a propagation, or an apply process, then the Streams client discards any row LCR that is not compatible with Oracle9i Database Release 2 (9.2).

The following is an example that is more appropriate for a positive rule set:

    BEGIN
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name => 'strmadmin.dml_compat_9_2',
        condition => ':dml.GET_COMPATIBLE() <= DBMS_STREAMS.COMPATIBLE_10_1()');
    END;
    /

If this rule is in the positive rule set for a Streams client, then the Streams client discards any row LCR that is not compatible with Oracle Database 10g Release 1 or earlier. That is, the Streams client processes any row LCR that is compatible with Oracle9i Database Release 2 (9.2) or Oracle Database 10g Release 1 (10.1) and satisfies the other rules in its rule sets, but it discards any row LCR that is not compatible with these releases.

Both of the rules in the previous examples evaluate efficiently. If you use schema rules or global rules created by the `DBMS_STREAMS_ADM` package to capture, propagate, apply, or dequeue LCRs, then rules such as these can be used to discard LCRs that are not supported by a particular database.

Note: You can determine which database objects in a database are not supported by Streams by querying the `DBA_STREAMS_UNSUPPORTED` data dictionary view. Instead of using the `DBMS_RULE_ADM` package to create rules with `GET_COMPATIBLE` conditions, you can use one of the procedures in the `DBMS_STREAMS_ADM` package to create such rules by specifying the `GET_COMPATIBLE` condition in the `AND_CONDITION` parameter. DDL LCRs always return `DBMS_STREAMS.COMPATIBLE_9_2`.

See Also: "Monitoring Compatibility in a Streams Environment" "Global Rules Example", "Schema Rule Example", and "System-Created Rules with Added User-Defined Conditions" Oracle Database Reference and Oracle Database Upgrade Guide for more information about the `COMPATIBLE` initialization parameter

#### Complex Rule Conditions

Complex rule conditions are rule conditions that do not meet the requirements for simple rule conditions described in "Simple Rule Conditions". In a Streams environment, the `DBMS_STREAMS_ADM` package creates rules with simple rule conditions only, assuming no custom conditions are added to the system-created rules. Table 6-3 describes the types of system-created rule conditions that you can create with the `DBMS_STREAMS_ADM` package. If you need to create rules with complex conditions, then you can use the `DBMS_RULE_ADM` package.

There is a wide range of complex rule conditions. The following sections contain some examples of complex rule conditions.

Note: Complex rule conditions can degrade rule evaluation performance. In rule conditions, if you specify the name of a database, then make sure you include the full database name, including the domain name.

##### Rule Conditions Using the NOT Logical Condition to Exclude Objects

You can use the `NOT` logical condition to exclude certain changes from being captured, propagated, applied, or dequeued in a Streams environment.

For example, suppose you want to specify rule conditions that evaluate to `TRUE` for all DML and DDL changes to all database objects in the `hr` schema, except for changes to the `hr.regions` table. You can use the `NOT` logical condition to accomplish this with two rules: one for DML changes and one for DDL changes. Here are the rule conditions for these rules:

    (:dml.get_object_owner() = 'HR' AND NOT :dml.get_object_name() = 'REGIONS')
    AND :dml.is_null_tag() = 'Y' ((:ddl.get_object_owner() = 'HR' OR :ddl.get_base_
    table_owner() =         'HR') AND NOT :ddl.get_object_name() = 'REGIONS') AND :ddl.is_
    null_tag() = 'Y'

Notice that object names, such as `HR` and `REGIONS` are specified in all uppercase characters in these examples. For rules to evaluate properly, the case of the characters in object names, such as tables and users, must match the case of the characters in the data dictionary. Therefore, if no case was specified for an object when the object was created, then specify the object name in all uppercase in rule conditions. However, if a particular case was specified through the use of double quotation marks when the objects was created, then specify the object name in the same case in rule conditions. However, the object name cannot be enclosed in double quotes in rule conditions.

For example, if the `REGIONS` table in the `HR` schema was actually created as `"Regions"`, then specify `Regions` in rule conditions that involve this table, as in the following example:

    :dml.get_object_name() = 'Regions'

You can use the Streams evaluation context when you create these rules using the `DBMS_RULE_ADM` package. The following example creates a rule set to hold the complex rules, creates rules with the previous conditions, and adds the rules to the rule set:

    BEGIN
      -- Create the rule set
      DBMS_RULE_ADM.CREATE_RULE_SET(
        rule_set_name       => 'strmadmin.complex_rules',
        evaluation_context  => 'SYS.STREAMS$_EVALUATION_CONTEXT');
      -- Create the complex rules
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name  => 'strmadmin.hr_not_regions_dml',
        condition  => ' (:dml.get_object_owner() = ''HR'' AND NOT ' ||
                      ' :dml.get_object_name() = ''REGIONS'') AND ' ||
                      ' :dml.is_null_tag() = ''Y'' ');
      DBMS_RULE_ADM.CREATE_RULE(
        rule_name  => 'strmadmin.hr_not_regions_ddl',
        condition  => ' ((:ddl.get_object_owner() = ''HR'' OR ' ||
                      ' :ddl.get_base_table_owner() =       ''HR'') AND NOT ' ||
                      ' :ddl.get_object_name() = ''REGIONS'') AND ' ||
                      ' :ddl.is_null_tag() = ''Y'' ');
      --  Add the rules to the rule set
      DBMS_RULE_ADM.ADD_RULE(
        rule_name      => 'strmadmin.hr_not_regions_dml',
        rule_set_name  => 'strmadmin.complex_rules');
      DBMS_RULE_ADM.ADD_RULE(
        rule_name      => 'strmadmin.hr_not_regions_ddl',
        rule_set_name  => 'strmadmin.complex_rules');
    END;
    /

In this case, the rules inherit the Streams evaluation context from the rule set.

Note: In most cases, you can avoid using complex rules with the `NOT` logical condition by using the `DBMS_STREAMS_ADM` package to add rules to the negative rule set for a Streams client

See Also: "System-Created Rules and Negative Rule Sets"

##### Rule Conditions Using the LIKE Condition

You can use the `LIKE` condition to create complex rules that evaluate to `TRUE` when a condition in the rule matches a specified pattern. For example, suppose you want to specify rule conditions that evaluate to `TRUE` for all DML and DDL changes to all database objects in the `hr` schema that begin with the pattern `JOB`. You can use the `LIKE` condition to accomplish this with two rules: one for DML changes and one for DDL changes. Here are the rule conditions for these rules:

    (:dml.get_object_owner() = 'HR' AND :dml.get_object_name() LIKE 'JOB%')
    AND :dml.is_null_tag() = 'Y'

    ((:ddl.get_object_owner() = 'HR' OR :ddl.get_base_table_owner() =       'HR')
    AND :ddl.get_object_name() LIKE 'JOB%') AND :ddl.is_null_tag() = 'Y'

#### Rule Conditions with Undefined Variables that Evaluate to NULL

During evaluation, an implicit variable in a rule condition is undefined if the variable value function for the variable returns `NULL`. An explicit variable without any attributes in a rule condition is undefined if the client does not send the value of the variable to the rules engine when it runs the `DBMS_RULE.EVALUATE` procedure.

Regarding variables with attributes, a variable is undefined if the client does not send the value of the variable, or any of its attributes, to the rules engine when it runs the `DBMS_RULE.EVALUATE` procedure. For example, if variable `x` has attributes `a` and `b`, then the variable is undefined if the client does not send the value of `x` and does not send the value of `a` and `b`. However, if the client sends the value of at least one attribute, then the variable is defined. In this case, if the client sends the value of `a`, but not `b`, then the variable is defined.

An undefined variable in a rule condition evaluates to `NULL` for Streams clients of the rules engine, which include capture processes, propagations, apply processes, and messaging clients. In contrast, for non-Streams clients of the rules engine, an undefined variable in a rule condition can cause the rules engine to return `maybe_rules` to the client. When a rule set is evaluated, `maybe_rules` are rules that might evaluate to `TRUE` given more information.

The number of `maybe_rules` returned to Streams clients is reduced by treating each undefined variable as `NULL`. Reducing the number of `maybe_rules` can improve performance if the reduction results in more efficient evaluation of a rule set when a message occurs. Rules that would result in `maybe_rules` for non-Streams clients can result in `TRUE` or `FALSE` rules for Streams clients, as the following examples illustrate.

##### Examples of Undefined Variables that Result in TRUE Rules for Streams Clients

Consider the following user-defined rule condition:

    :m IS NULL

If the value of the variable `m` is undefined during evaluation, then a maybe rule results for non-Streams clients of the rules engine. However, for Streams clients, this condition evaluates to `TRUE` because the undefined variable `m` is treated as a `NULL`. You should avoid adding rules such as this to rule sets for Streams clients, because such rules will evaluate to `TRUE` for every message. So, for example, if the positive rule set for a capture process has such a rule, then the capture process might capture messages that you did not intend to capture.

Here is another user-specified rule condition that uses a Streams `:dml` variable:

    :dml.get_object_owner() = 'HR' AND :m IS NULL

For Streams clients, if a message consists of a row change to a table in the `hr` schema, and the value of the variable `m` is not known during evaluation, then this condition evaluates to `TRUE` because the undefined variable `m` is treated as a `NULL`.

##### Examples of Undefined Variables that Result in FALSE Rules for Streams Clients

Consider the following user-defined rule condition:

    :m = 5

If the value of the variable `m` is undefined during evaluation, then a maybe rule results for non-Streams clients of the rules engine. However, for Streams clients, this condition evaluates to `FALSE` because the undefined variable `m` is treated as a `NULL`.

Consider another user-specified rule condition that uses a Streams `:dml` variable:

    :dml.get_object_owner() = 'HR' AND :m = 5

For Streams clients, if a message consists of a row change to a table in the `hr` schema, and the value of the variable `m` is not known during evaluation, then this condition evaluates to `FALSE` because the undefined variable `m` is treated as a `NULL`.

See Also: "Rule Set Evaluation"

#### Variables as Function Parameters in Rule Conditions

Oracle recommends that you avoid using `:dml` and `:ddl` variables as function parameters for rule conditions. The following example uses the `:dml` variable as a parameter to a function named `my_function`:

    my_function(:dml) = 'Y'

Rule conditions such as these can degrade rule evaluation performance and can result in the capture or propagation of extraneous Streams data dictionary information.

See Also: "The Streams Data Dictionary"

### User-Created Evaluation Contexts

You can use a custom evaluation context in a Streams environment. Any user-defined evaluation context involving LCRs must include all the variables in `SYS.STREAMS$_EVALUATION_CONTEXT`. The type of each variable and its variable value function must be the same for each variable as the ones defined in `SYS.STREAMS$_EVALUATION_CONTEXT`. In addition, when creating the evaluation context using `DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT`, the `SYS.DBMS_STREAMS_INTERNAL.EVALUATION_CONTEXT_FUNCTION` must be specified for the `evaluation_function` parameter. You can alter an existing evaluation context using the `DBMS_RULE_ADM.ALTER_EVALUATION_CONTEXT` procedure.

You can find information about an evaluation context in the following data dictionary views:

- `ALL_EVALUATION_CONTEXT_TABLES`

- `ALL_EVALUATION_CONTEXT_VARS`

- `ALL_EVALUATION_CONTEXTS`

If necessary, you can use the information in these data dictionary views to build a new evaluation context based on the `SYS.STREAMS$_EVALUATION_CONTEXT`.

Note: Avoid using variable names with special characters, such as $ and #, to ensure that there are no conflicts with Oracle-supplied evaluation context variables.

See Also: Oracle Database Reference for more information about these data dictionary views